package amushen.base;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * deal with database
 * 
 * usage:
 * 1.The connection is not auto commit , so after updating db ,need to close conn.
 * 2.query(): sql.properties stores every db opreation and query statement.
 *            suggest use sqlkey to load sql statement.
 * 			  if select's condition is not
 * 			  needed ,the pass NOCONDITION to ignore this condition.
 * 			  TODO Paged querying parameter is not tested.
 * 			  TODO total result is not solved for paged querying
 * 3.update(): for insert delete statement
 * 
 * @author shennan
 * 
 */
public class DBUtil {
	//for sql statement , when don't need condition ,use this instead of.
	public final static String NOCONDITION="NULL";
	
	private Connection conn;
	private ResultSet rs;
	private PreparedStatement ps;
	
	public DBUtil() {
		try {
			// Load the HSQL Database Engine JDBC driver
			// hsqldb.jar should be in the class path or made part of the
			// current jar
			Class.forName("org.hsqldb.jdbc.JDBCDriver");

			// connect to the database. This will load the db files and start
			// the
			// database if it is not alread running.
			// db_file_name_prefix is used to open or create files that hold the
			// state
			// of the db.
			// It can contain directory names relative to the
			// current working directory
//			conn = DriverManager.getConnection("jdbc:hsqldb:" + "test", // filenames
//					"sa", // username
//					""); // password
			
			conn = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/","sa","");
			conn.setAutoCommit(false);
			
			SqlHelper.loadSqls();
			
		} catch (Exception e) {
			e.printStackTrace();
			Logger.getLogger(DBUtil.class.getName()).log(Level.SEVERE,
					"Can't open db." + e.getMessage());
		}
	}
	
	
	/**
	 * return the total rows of this query.
	 * 
	 * replace select columns name to count(*) to return the total rows.
	 * For paging
	 * @param sqlKey
	 * @param parames
	 * @return
	 */
	public int queryTotalRows(String sqlKey,Object[] parames){
		
		return 0;
	}
	
	public List query(String sqlKey,Object[] params){
		return query(sqlKey,params,-1,-1);
	}
	
	/**
	 * use for SQL command SELECT
	 * @param sqlKey load sql from sql.properties file
	 * @param params every parameter
	 * @param pageNo current page's number.(from 0)
	 * @param perPage count about every page
	 * @return
	 * @throws SQLException
	 */
	public List query(String sqlKey,Object[] params,int pageNo,int perPage){
		String sql=SqlHelper.getSQL(sqlKey);		
		sql=omitNullCondition(sql, params);
		sql=omitBracket(sql);
		Logger.getLogger(DBUtil.class.getName()).log(Level.INFO,"query:"+sqlKey+":"+sql);
		return queryBySql(sql,params,pageNo,perPage);
	}
	
	public List queryBySql(String sql,Object[] params,int pageNo,int perPage){
		if(sql==null||sql.length()<1)return new ArrayList();
		//sql=omitNullCondition(sql, params);
		//sql=omitBracket(sql);		
		
		if(sql!=""){
			try{
				if(pageNo!=-1&&perPage!=-1){
				//only for some database such as :oracle,mysql,hsqldb
				//page the resultset					
					int offset=pageNo*perPage;
					sql=sql.replace("select", "select limit "+offset+" "+perPage);
				}
				Logger.getLogger(DBUtil.class.getName()).log(Level.INFO,"queryBySql:"+sql);
				
				ps = conn.prepareStatement(sql);
				if(params!=null){
					setParameters(ps, params);
				}				
				rs = ps.executeQuery();				
				return dump(rs);
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		return null;
	}
	
	
	/**
	 * get rid of condition where the value is null.
	 * e.g.
	 * select * from users where [id=?][,name=?];
	 * if the second value of params is null then the sql translate to
	 * select * from users where id=?;
	 * 	 * 
	 * @param oriSql
	 * @param params
	 * @return
	 */
	public String omitNullCondition(String oriSql,Object[] params){
		if(params==null||oriSql==null||params.length<1)return oriSql;
		String sql="";
		int j=0;//the index in oriSql
		int k=-1; //index of sql's ]
		for(int i=0;i<params.length;i++){
			//current ? 's index
			while(j<oriSql.length()&&oriSql.charAt(j)!='?'){
				sql+=oriSql.charAt(j);
				j++;
			}			
			if(j>=oriSql.length())
				return sql;
			else
				sql+="?";
			
			if(params[i]==NOCONDITION){
				//omit this condition
				k=sql.length()-1;
				while(k>0&&sql.charAt(k)!='[')k--;
				sql=sql.substring(0, k);
				while(j<oriSql.length()&&oriSql.charAt(j)!=']')j++;
				j++;
			}else{
				j++;
			}
			
		}
		sql=sql+oriSql.substring(j);
		return sql;
	}
	
	/**
	 * omit square bracket 
	 * e.g. 
	 * select * from user where [id=?][,user=?]
	 * -> select * from user where id=?,user=?
	 * @param oriSql
	 * @return
	 */
	public String omitBracket(String oriSql){
		if(oriSql==null)return oriSql;
		String sql=oriSql.replaceAll("\\[", "");
		sql=sql.replaceAll("\\]", "");
		return sql;		
	}
	
	public List dump(ResultSet rs)throws SQLException {

        // the order of the rows in a cursor
        // are implementation dependent unless you use the SQL ORDER statement
        ResultSetMetaData meta   = rs.getMetaData();
        int               colmax = meta.getColumnCount();
        int               i;
//        Object            o = null;
        Object[]		  rowdata = null;
        ArrayList 		  ret = new ArrayList();
        
        // the result set is a cursor into the data.  You can only
        // point to one row at a time
        // assume we are pointing to BEFORE the first row
        // rs.next() points to next row and returns true
        // or false if there is no next row, which breaks the loop
        for (; rs.next(); ) {
        	rowdata=new Object[colmax];
        	
            for (i = 0; i < colmax; ++i) {
                rowdata[i]= rs.getObject(i + 1);    // Is SQL the first column is indexed
            }
            ret.add(rowdata);
        }
        return ret;
    }        

	/**
	 * use for SQL commands CREATE, DROP, INSERT and UPDATE
	 * @param sqlKey
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public int update(String sqlKey,Object[] params){
		int i=0;
		try{			
			String sql=SqlHelper.getSQL(sqlKey);
			Logger.getLogger(DBUtil.class.getName()).log(Level.INFO,"update:"+sqlKey+","+sql);
			
			ps=conn.prepareStatement(sql);
			if(params!=null){
				setParameters(ps, params);
			}
			
			i = ps.executeUpdate(); // run the query
	
			if (i == -1) {
				System.out.println("db error : " + SqlHelper.getSQL(sqlKey));
			}			
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}
		return i;
	}
	
	
	private void setParameters(PreparedStatement ps,Object[] paras)throws SQLException{
		int i=1;
		if(paras!=null){
			for(int j=0;j<paras.length;j++){
				Object p=paras[j];
				if(p==NOCONDITION)continue;
				if(p==null){
					ps.setObject(i++, null);
				}else if(p instanceof String){
					ps.setString(i++, (String)p);
				}else if(p instanceof Integer){
					ps.setInt(i++, ((Integer)p).intValue());
				}else if(p instanceof Long){
					ps.setLong(i++, ((Long)p).longValue());
				}else if(p instanceof Float){
					ps.setFloat(i++, ((Float)p).floatValue());
				}else if(p instanceof Double){
					ps.setDouble(i++, ((Double)p).doubleValue());
				}else if(p instanceof Boolean){
					ps.setBoolean(i++, ((Boolean)p).booleanValue());						
				}else if(p instanceof java.sql.Date){
					ps.setDate(i++, (java.sql.Date)p);
				}else if(p instanceof java.util.Date){
					ps.setDate(i++, new java.sql.Date(((java.util.Date)p).getTime()));
				}
			}
		}
	}
	
	public void commit(){
		if(conn!=null){
			try {
				conn.commit();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void rollback(){
		if(conn!=null){
			try {
				conn.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	//close db
	public void close(){
		try {
			if(rs!=null){
				rs.close();
			}
			if(ps!=null){
				ps.close();
			}
			if(conn!=null){				
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		rs=null;
		ps=null;
		conn=null;
	}
	
		
	/**
	 * use for load sqls from file
	 * @author shen.n
	 *
	 */
	private static class SqlHelper{
		private static SqlHelper ins=null;
		private static Properties sqls=null;
		private SqlHelper(){
			try {
				loadSqls();
			} catch (IOException e) {			
				e.printStackTrace();
			}
		}
		
		public synchronized static void loadSqls() throws IOException{
			sqls=null;
			sqls=Resources.getResourceAsProperties("/amushen/sql.properties");
		}
		
		public synchronized static String getSQL(String key){
			if(ins==null){
				ins=new SqlHelper();
			}
			if(sqls==null)return "";
			return sqls.getProperty(key);
		}
		
		
	}
}
